A database is a collection of stored data that is logically related and that is accessible by one or more users. A popular type of database is the relational database management system (RDBMS), which includes relational tables made up of rows and columns. Each row represents an occurrence of an entity defined by a table, with an entity being a person, place, or thing about which the table contains information. To extract data from, or to update, a relational table, queries according to a standard database query language (e.g., Structured Query Language or SQL) are used. A table (also referred to as a relation) is made up of multiple rows (also referred to as tuples). Each row (or tuple) includes multiple columns (also referred to as attributes).
Another data structure typically associated with relations in a relational database system is a view, which is a derived relation formed by performing a function on one or more base relations. Rather than storing the view, the function is typically recomputed each time the view is referenced. This type of view is referred to as an “ordinary view.”
Unlike an ordinary view, a materialized view is a pre-computed, stored query result that can be used to respond to certain queries instead of reconstructing the results directly from the base relations. As with the ordinary view, a function is performed on the base relations to derive the materialized view. However, because the materialized view is stored, fast access to the data is possible without recomputing the view.
After the materialized view is created, subsequent queries are able to use the materialized view, where appropriate, to increase query processing speed. Materialized views can be used to assemble data that come from many different relations. One type of view is the join view, which stores join results of multiple base relations. If an optimizer in a database system determines that a given query can be satisfied by using a materialized view, the optimizer performs query rewrite to convert the query to a form that causes rows of the materialized view (instead of base relations) to be accessed. In query rewrite, reference to base table(s) in the query is replaced with reference to the materialized view(s).
Although materialized views can enhance processing speed for queries, materialized views are also associated with maintenance costs. Materialized view maintenance refers to the modification of a materialized view in response to modification of associated base relations(s). A materialized view is modified when any of its underlying base relations is modified. As base relations are changed through insertion of new tuples, deletion of tuples, or update of existing tuples, the corresponding rows in the materialized view are changed to avoid becoming stale.
The cost effectiveness of storing materialized views thus depends on how frequently such materialized views can be used to satisfy subsequent queries.